<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
ini_set('max_execution_time', '0');

/*******************
***  库存预警基础信息存储
*** 2017/07/28
**********/
class Stockwarning
{

	 public function __construct()
    {
        $this->CI =& get_instance();
        $this->db = $this->CI->load->database('default', TRUE);
		$this->CI->load->model('Function_m','fun');
		$this->CI->load->model('Order_m','order');
		
    }
	//读取直发销售
	public function getallsale()
	{
		$t1 = microtime(true);
		//所有销售先设置为空
		$this->db->query("UPDATE db_goods SET sales1=0,sales2=0,sales3=0,dailysales=0,alertnum=0,fbasale1=0,fbasale2=0,fbasale3=0");

		//读取60天内所有销售的SKU信息
		$days = $this->getdata();
		$starttime = date("Y-m-d",time()-60*86400);
		$endtime = date("Y-m-d");
		$sql ="SELECT goods_id,goods_sn FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id WHERE B.dates >= '".$starttime."' AND B.dates <= '".$endtime."' AND B.order_status !=7 AND B.order_status !=6 GROUP BY goods_id";
		$query = $this->db->query($sql);
        $dates = $query->result_array();
		$post="";
		foreach($dates as $keyst=>$rs)
		{
				foreach($days as $key=>$day)
				{
					$res  = $this->CI->fun->get_goods_sale($rs['goods_id'],$day['starttime'],$day['endtime']);
					if($key==0)
					{
						$sales1 = $res;
					}
					if($key==1)
					{
						$sales2 = $res;
					}
					if($key==2)
					{
						$sales3 =$res;
					}
				}
				$post[$keyst]['sales1'] =$sales1;
				$post[$keyst]['sales2'] =$sales2;
				$post[$keyst]['sales3'] =$sales3;
				$post[$keyst]['goods_id'] = $rs['goods_id'];
				//$this->db->update('db_goods',$post," goods_id =".$rs['goods_id']);
		}
		$this->db->update_batch('db_goods', $post, 'goods_id');//批量更新

		$t2 = microtime(true);
		$vals='直发执行耗时 '.round($t2-$t1,3).'秒&nbsp;&nbsp;&nbsp;';
		echo $vals;

		return true;
	}
	
	//读取FBA的销量
	public function getFBAallsale()
	{
		$t1 = microtime(true);
		$days = $this->getdata();
		$starttime = date("Y-m-d",time()-60*86400);
		$endtime = date("Y-m-d");
		$sql ="SELECT goods_id,productid FROM db_amzordertmpsell A LEFT JOIN db_amzordertmp B ON A.ordertmpid = B.id WHERE B.dates >= '".$starttime."' AND B.dates <= '".$endtime."' AND B.status =1 AND A.goods_id >0 GROUP BY goods_id";
		$query = $this->db->query($sql);
        $dates = $query->result_array();
		$post="";
		foreach($dates as $keyst=>$rs)
		{
				foreach($days as $key=>$day)
				{
					$res  = $this->CI->fun->get_goods_fba_sale($rs['goods_id'],$day['starttime'],$day['endtime']);
					if($key==0)
					{
						$sales1 = $res;
					}
					if($key==1)
					{
						$sales2 = $res;
					}
					if($key==2)
					{
						$sales3 =$res;
					}
				}
				$post[$keyst]['fbasale1'] = $sales1;
				$post[$keyst]['fbasale2'] = $sales2;
				$post[$keyst]['fbasale3'] = $sales3;
				$post[$keyst]['goods_id'] = $rs['goods_id'];
		}
		$this->db->update_batch('db_goods', $post, 'goods_id');//批量更新
		$t2 = microtime(true);
		$vals='FBA执行耗时 '.round($t2-$t1,3).'秒&nbsp;&nbsp;&nbsp;';
		echo $vals;
		return true;		
		
	}

	//日均销量数,和预警数
	//外采购7天
	//公司产品60
	public function dailysales()
	{

		/***********
			日均销量：
				①新品进货前7天:依数学平均数，例如:前7天卖21个，则DMS=3。
				①三十天之后:DMS=前一天的DMSX0.9+当日销量X0.1。
		
		***************/
		$t1 = microtime(true);
		$sql = "SELECT goods_id,goods_sku,g_inventory_day,g_supplier,g_updatetime FROM `db_goods` WHERE (sales3>0 or fbasale3>0)";
		$query = $this->db->query($sql);
        $dates = $query->result_array();
		$post="";
		foreach($dates as $keyst=>$rs)
		{
				$d1 = time();
				$d2 = strtotime($rs['g_updatetime']);
				$Days = round(($d1-$d2)/3600/24);
			if($rs['g_supplier'] ==3 )	//外采购
			{
				$purchaseotherday  = intval($this->CI->config->item('purchaseotherday1'))?intval($this->CI->config->item('purchaseotherday1')):7;	//外采购的统计天数
				$g_inventory_day = $rs["g_inventory_day"]?$rs["g_inventory_day"]:15;
				
				/*
				if($Days<$purchaseother)
				{
					$purchaseotherday = $Days;
				}else{
					$purchaseotherday = $purchaseother;
				}
				*/
			}else{			//工厂
				$purchaseother  = intval($this->CI->config->item('purchaseotherday2'))?intval($this->CI->config->item('purchaseotherday2')):30;	//工厂统计天数
				
				//如果商品刚进来，小于30天，按正常天数计算
				if($Days<$purchaseother)
				{
					$purchaseotherday = $Days;
				}else{
					$purchaseotherday = $purchaseother;
				}

				
				if($rs["g_inventory_day"]==15 || !$rs["g_inventory_day"])
				{
					$g_inventory_day = 30;
				}else{
					$g_inventory_day = $rs["g_inventory_day"];
				}
			}
			
			$starttimest = date("Y-m-d",time()-$purchaseotherday*86400);
			$endtimest = date("Y-m-d");
			
			$rescount1  = $this->CI->fun->get_goods_sale($rs['goods_id'],$starttimest,$endtimest);//所有的销售
			$rescount2  = $this->CI->fun->get_goods_fba_sale($rs['goods_id'],$starttimest,$endtimest);//所有FBA的销售
			
				
			$onecangsale  = $this->CI->fun->get_goods_sale($rs['goods_id'],$starttimest,$endtimest,'',5);//1号仓销量

			$rescount = $rescount1+$rescount2;
			if($purchaseotherday>0){
				$dailysales = round($rescount/$purchaseotherday,2);
				$onecang	= round($onecangsale/$purchaseotherday,2);
			}else{
				$dailysales = 0;
				$onecang    = 0;
			}

			

			$post[$keyst]['onecangsales'] = $onecang;	//(1号仓)
			$post[$keyst]['dailysales'] =$dailysales; //日销量（FBA+直发）
			$post[$keyst]['alertnum'] =ceil($dailysales*$g_inventory_day); //警戒库存数

			//总销量

			$post[$keyst]['goods_id'] = $rs['goods_id'];

		}
		$this->db->update_batch('db_goods', $post, 'goods_id');//批量更新
		$t2 = microtime(true);
		$vals='统计执行耗时 '.round($t2-$t1,3).'秒&nbsp;&nbsp;&nbsp;';
		echo $vals;
		return true;	
	}
	
	


	public function getstockw()
	{
		//先获取所有商品的销量!
		//每天跑一边数据
		//获取SKU 7天 30天 60天内的销量
		//先计算90天内有没有销量
		$t1 = microtime(true);
		
		//所有销售先设置为0
		
		
			
		$days = $this->getdata();
		$goodslist = $this->db->select('goods_id,goods_sku,g_supplier,g_inventory_day')->order_by("goods_id","ASC")->get("db_goods")->result_array();
		foreach($goodslist as $key=>$rs)
		{
			$rsdb = $this->CI->fun->get_goods_sale($rs['goods_id'],$starttime,$endtime);
			if($rsdb==0)
			{
				//90天内销量为0
				$post="";
				$post['sales1'] = 0;
				$post['sales2'] = 0;
				$post['sales3'] = 0;
				$post['dailysales'] =0;
				$post['alertnum'] =0;
				$this->db->update('db_goods',$post," goods_id =".$rs['goods_id']);
			}else{
				//查询所设定的销售值
				$post="";
				foreach($days as $key=>$day)
				{
					$res  = $this->CI->fun->get_goods_sale($rs['goods_id'],$day['starttime'],$day['endtime']);
					if($key==0)
					{
						$sales1 = $res;
					}
					if($key==1)
					{
						$sales2 = $res;
					}
					if($key==2)
					{
						$sales3 =$res;
					}
				}
					//日均销量数
					//外采购7天
					//公司产品60
					if($rs['g_supplier'] ==3 )	//外采购
					{
						$purchaseotherday  = intval($this->CI->config->item('purchaseotherday1'))?intval($this->CI->config->item('purchaseotherday1')):7;	//外采购的统计天数
						$g_inventory_day = $rs["g_inventory_day"]?$rs["g_inventory_day"]:15;
					}else{			//工厂
						$purchaseotherday  = intval($this->CI->config->item('purchaseotherday2'))?intval($this->CI->config->item('purchaseotherday2')):60;	//工厂统计天数
						if($rs["g_inventory_day"]==15 || !$rs["g_inventory_day"])
						{
							$g_inventory_day = 60;
						}else{
							$g_inventory_day = $rs["g_inventory_day"];
						}
					}

					$starttimest = date("Y-m-d",time()-$purchaseotherday*86400)."";
					$endtimest = date("Y-m-d")."";
					$rescount  = $this->CI->fun->get_goods_sale($rs['goods_id'],$starttimest,$endtimest);
					$dailysales = round($rescount/$purchaseotherday,2);
					$post['sales1'] =$sales1;
					$post['sales2'] =$sales2;
					$post['sales3'] =$sales3;
					$post['dailysales'] =$dailysales; //日销量
					$post['alertnum'] =ceil($dailysales*$g_inventory_day); //警戒库存数
					
				
				$this->db->update('db_goods',$post," goods_id =".$rs['goods_id']);
			}
		}
		

		


		$t2 = microtime(true);
		$vals='页面执行耗时 '.round($t2-$t1,3).'秒&nbsp;&nbsp;&nbsp;';
		echo $vals;
		echo "OK";

	}

	
	/************************
		获取需要统计的日期
	*************************/
	function getdata()
	{
		$saleskumonth  = trim($this->CI->config->item('saleskumonth'));
		if($saleskumonth)
		{
			$days = explode("|",$saleskumonth);
			$i = 0;
			foreach($days as $key => $rs)
			{
				$starttime = date("Y-m-d",time()-$rs*86400)." 00:00:00";
				$endtime = date("Y-m-d")." 23:59:59";
				
				$oneDay[$i]["starttime"]= $starttime;
				$oneDay[$i]["endtime"] = $endtime;

				$i=$i+1;
			}
			
		}else{
			$days[] =30;
			$days[] =60;
			$days[] =60;
			$i = 0;
			foreach($days as $key => $rs)
			{
				$starttime = date("Y-m-d",time()-$rs*86400)." 00:00:00";
				$endtime = date("Y-m-d")." 23:59:59";
				
				$oneDay[$i]["starttime"]= $starttime;
				$oneDay[$i]["endtime"] = $endtime;
				$i=$i+1;
			}

		}
		return $oneDay;

	}

	/**********五位SKU换成八位定制**************/
	function autochangsku()
	{
		//查询所有已支付或配货中的五位工厂SKU
		$times = date("Y-m-d");
		$sql = "SELECT goods_sn,order_id,goods_id,rec_id,sale_remark FROM db_order_sell WHERE order_id in( select order_id from db_order_list where (order_status = 2 or order_status=3 )  AND dates ='".$times."' ) ";
		$query = $this->db->query($sql);
        $dates = $query->result_array();
		foreach($dates as $key=>$rs)
		{
			$rsdb = $this->db->get_one("SELECT * FROM db_goods WHERE goods_id='".$rs['goods_id']."' AND g_sale_status in(4,6,7) AND length(goods_parent_sku)=8 AND (locate(11,goods_parent_sku)=1 or locate(12,goods_parent_sku)=1 ) and length(goods_sku)<8  ");
			if($rsdb)
			{
			
				//如果有库存，不转换
				$goods_id = $rs['goods_id'];
				$rStock = $this->db->get_one("SELECT * from db_goods_stock where goods_id='".$goods_id."' and stock >0 and storage_id in(1,2)");
				if($rStock)
				{
				
				}else{
					//自动转换成8位SKU
					$order_id = $rs["order_id"];
					$post="";
					$post["goods_id"] = $rsdb["goods_parentID"];
					$post["goods_sn"] = $rsdb["goods_parent_sku"];
					$post["goods_name"] = $rsdb["goods_name"];
					$costprice = $this->CI->fun->get_Goods_costprice($rsdb["goods_parent_sku"]);//获取商品成本
					$post["costprice"] = $costprice;
					$post["goods_weight"] = $rsdb["g_weight"];
					$this->db->update("db_order_sell",$post,"rec_id='".$rs["rec_id"]."'");
					$this->CI->order->recalculategoodsprice($order_id);//重新计算成本
					$this->CI->fun->goods_update_log($order_id, "五位[{$rs[goods_sn]}]关联停产，自动转换成八位定制[$rsdb[goods_parent_sku]],成本->{$costprice}", 3); 
				}
			}
		}
		echo "OK";
	}

	/****海外FBA头程计算数据提取*****************/
	function getoversea_transportlist()
	{
		//获取所有已完成的运输任务
		$list = $this->db->select('tasknumber,id,shopname,destination,freight,actual_freight,totalmoney,totalamount')->get_where("t_transportationtask","  status = 5 AND (freight >0 or actual_freight >0)")->result_array();
		if(count($list)>0)
		{
			foreach($list as $key=>$res){
				$rsdb = $this->db->get_one("select * from t_transportlist_log where taskId = '".$res["id"]."'");	//如果已经计算过了，不再计算
				if(!$rsdb)
				{
					/*开始计算头程运费*/

					if($res["actual_freight"])	//计算总运费成本
					{
						$deliver_totalfee = $res['actual_freight'] + $res['totalmoney']; //预估费用+包装成本（多个箱子成本）
					}else{
						$deliver_totalfee = $res['freight'] + $res['totalmoney']; //预估费用+包装成本（多个箱子成本）
					}
					$rsbox = $this->db->get_one("SELECT sum(weight) as totalweight from t_transportbox where task_id = '".$res["id"]."'");
					$deliver_totalweight = $rsbox['totalweight'];//总重量
					
					if($deliver_totalweight>0)
					{
						$unitfee = round($deliver_totalfee/$deliver_totalweight,2);//计算每千克费用
					}else{
						continue;
					}

					$sql = "SELECT A.*,B.lengthnum,B.widthnum,B.heightnum,C.goods_id,C.g_weight,D.shopname,D.destination FROM t_transportlist A LEFT JOIN t_overseaproduct B ON A.skuid = B.id LEFT JOIN db_goods C on A.sku = C.goods_sku LEFT JOIN t_transportationtask D ON A.taskid = D.id  WHERE A.taskid = '".$res['id']."' ";
					$query = $this->db->query($sql);
					$rs_box = $query->result_array();
					$ni = 0;
					foreach($rs_box as $v)
					{
						$arr_log = "";
						$db_weight = $v['g_weight']/1000; //db_goods 重量为g，转换单位kg
						$real_weight = round(($v['lengthnum']*$v['widthnum']*$v['heightnum'])/6000,2);//保留两位小数
						if($db_weight>=$real_weight){
							$weight = $db_weight;
						}else{
							$weight = $real_weight;	//商品的重量
						}

						$deliver_costprice = $unitfee*$weight; //实际运输成本
						$rsdbdestination = $this->db->get_one("select id from t_overseawarehouse where name like '".$v["destination"]."'");	//目的地ID
						
						$sql_log = "SELECT count(goods_sku) as count,sum(price) as totalprice FROM t_transportlist_log WHERE goods_sku = '{$v['sku']}' GROUP BY goods_sku ";
						$rs_log  = $this->db->query($sql_log)->row_array();
						if($rs_log){
							$count = $rs_log['count']+1;
							$avgprice = $rs_log['totalprice']+$deliver_costprice;
							$avgprice = round($avgprice/$count,2);
							$this->db->update('t_overseaproduct',array('deliverprice'=>$avgprice),"id = '".$v['skuid']."'");
							$arr_log['goods_sku'] = $v['sku'];
							$arr_log['taskId'] = $v['taskid'];
							$arr_log['price'] = $deliver_costprice;
							$arr_log['avgprice'] = $avgprice;
							$arr_log['transport_frequency'] = $count;    //当前该sku运输次数
							$arr_log['createTime'] = date("Y-m-d H:i:s");
							if($v["shopname"]=='')
							{
								$arr_log['shopname'] = $v['destination'];
							}else{
								$arr_log['shopname'] = $v['shopname'];
							}
							$arr_log['destination'] =$v['destination'];
							$arr_log['destinationID'] =$rsdbdestination['id']?$rsdbdestination['id']:0;
							$this->db->insert('t_transportlist_log',$arr_log);
							//print_r(array($deliver_costprice));exit;
						}else{
							$this->db->update('t_overseaproduct',array('deliverprice'=>$deliver_costprice),"id = '".$v['skuid']."'");
							$arr_log['goods_sku'] = $v['sku'];
							$arr_log['taskId'] = $v['taskid'];
							$arr_log['price'] = $deliver_costprice;
							$arr_log['avgprice'] = $deliver_costprice;
							$arr_log['transport_frequency'] = 1;    //当前该sku运输次数,第一次
							$arr_log['createTime'] = date("Y-m-d H:i:s");
							if($v["shopname"]=='')
							{
								$arr_log['shopname'] = $v['destination'];
							}else{
								$arr_log['shopname'] = $v['shopname'];
							}
							$arr_log['destination'] =$v['destination'];
							$arr_log['destinationID'] =$rsdbdestination['id']?$rsdbdestination['id']:0;
							$this->db->insert('t_transportlist_log',$arr_log);
							//print_r(array($deliver_costprice));exit;
						}

						$ni = $ni+1;

					}

				

					

				}
			}

				//print_r($arr_log);
				

					//$this->db->insert_bacth('t_transportlist_log',$arr_log);
					
		}
		
	}
}